CREATE TABLE IF NOT EXISTS sys_dept
(
    id        NUMBER(10, 0) PRIMARY KEY,
    parent_id NUMBER(10, 0) DEFAULT 0,
    name      VARCHAR2(32) NOT NULL,
    sort      NUMBER(10, 0) DEFAULT 0
);
CREATE SEQUENCE sys_dept_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_dept
    BEFORE INSERT
    ON sys_dept
    FOR EACH ROW BEGIN SELECT sys_dept_seq.Nextval INTO :NEW.ID FROM DUAL;
END;

-- 角色
CREATE TABLE IF NOT EXISTS sys_role
(
    id        NUMBER(10, 0) PRIMARY KEY,
    role_name VARCHAR2(32) NOT NULL,
    role_code VARCHAR2(32) NOT NULL,
    role_desc VARCHAR2(255),
    note      VARCHAR2(32)
);
CREATE SEQUENCE sys_role_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_role
    BEFORE INSERT
    ON sys_role
    FOR EACH ROW BEGIN SELECT sys_role_seq.Nextval INTO :NEW.ID FROM DUAL;
END;


-- 系统用户
CREATE TABLE IF NOT EXISTS sys_user
(
    id        NUMBER(10, 0) PRIMARY KEY,
    username  VARCHAR2(32)  NOT NULL,
    password  VARCHAR2(32)  NOT NULL,
    email     VARCHAR2(32),
    mobile    VARCHAR2(32),
    real_name VARCHAR2(32),
    dept_id   NUMBER(10, 0),
    dept_name VARCHAR2(32),
    role_id   NUMBER(10, 0) NOT NULL,
    role_name VARCHAR2(32)  NOT NULL,
    status    NUMBER(10, 0) NOT NULL
);
CREATE SEQUENCE sys_user_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_user
    BEFORE INSERT
    ON sys_user
    FOR EACH ROW BEGIN SELECT sys_user_seq.Nextval INTO :NEW.ID FROM DUAL;
END;

-- 菜单
CREATE TABLE IF NOT EXISTS sys_menu
(
    id        NUMBER(10, 0) PRIMARY KEY,
    parent_id NUMBER(10, 0) DEFAULT 0,
    name      VARCHAR2(32) NOT NULL,
    code      VARCHAR2(32) NOT NULL,
    url       VARCHAR2(32),
    perm      VARCHAR2(128),
    type      NUMBER(10, 0),
    is_hidden NUMBER(4, 0)  DEFAULT 0,
    icon      VARCHAR2(32),
    sort      NUMBER(10, 0) DEFAULT 0
);
CREATE SEQUENCE sys_menu_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_menu
    BEFORE INSERT
    ON sys_menu
    FOR EACH ROW BEGIN SELECT sys_menu_seq.Nextval INTO :NEW.ID FROM DUAL;
END;

-- 角色与菜单对应关系
CREATE TABLE IF NOT EXISTS sys_role_menu
(
    id      NUMBER(10, 0) PRIMARY KEY,
    role_id NUMBER(10, 0) NOT NULL,
    menu_id NUMBER(10, 0) NOT NULL
);
CREATE SEQUENCE sys_role_menu_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_role_menu
    BEFORE INSERT
    ON sys_role_menu
    FOR EACH ROW BEGIN SELECT sys_role_menu_seq.Nextval INTO :NEW.ID FROM DUAL;
END;

-- 系统日志
CREATE TABLE IF NOT EXISTS sys_log
(
    id             NUMBER(10, 0) PRIMARY KEY,
    type           NUMBER(4, 0),
    user_id        NUMBER(10, 0),
    username       VARCHAR2(32),
    operation      VARCHAR2(64),
    method         VARCHAR2(255),
    params         LONG,
    consuming_time NUMBER(10, 0),
    ip             VARCHAR2(32),
    ex_detail      LONG,
    ex_desc        VARCHAR2(2000),
    create_time    TIMESTAMP
);
CREATE SEQUENCE sys_log_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_log
    BEFORE INSERT
    ON sys_log
    FOR EACH ROW BEGIN SELECT sys_log_seq.Nextval INTO :NEW.ID FROM DUAL;
END;

-- 系统字典
CREATE TABLE IF NOT EXISTS sys_dict
(
    id          NUMBER(10, 0) PRIMARY KEY,
    dict_key    VARCHAR2(50) NOT NULL,
    dict_value  VARCHAR2(1024),
    description VARCHAR2(255),
    note        VARCHAR2(255)
);
CREATE SEQUENCE sys_dict_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_dict
    BEFORE INSERT
    ON sys_dict
    FOR EACH ROW BEGIN SELECT sys_dict_seq.Nextval INTO :NEW.ID FROM DUAL;
END;


-- 系统定时任务
CREATE TABLE IF NOT EXISTS sys_task
(
    id          NUMBER(10, 0) PRIMARY KEY,
    task_name   VARCHAR2(64)  NOT NULL,
    class_name  VARCHAR2(255) NOT NULL,
    cron        VARCHAR2(50)  NOT NULL,
    params      VARCHAR2(1000),
    description VARCHAR2(255),
    note        VARCHAR2(255)
);
CREATE SEQUENCE sys_task_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE TRIGGER increase_sys_task
    BEFORE INSERT
    ON sys_task
    FOR EACH ROW BEGIN SELECT sys_task_seq.Nextval INTO :NEW.ID FROM DUAL;
END;
